package DB;

import Model.*;

import java.sql.*;
import java.util.ArrayList;

public class DBVare 
{
	private Connection con;
	private DBUdlejningstype dbUdl;
	
	private static DBVare instance = null;
    
    public static DBVare getInstance()
    {
        if (instance == null)
        {
          instance = new DBVare();
        }
        return instance;
    }
	
	public DBVare()
	{
		con = DBConnection.getInstance().getDBcon();
		dbUdl = DBUdlejningstype.getInstance();
	}
	
	private void create(String wClause)
	{
	    String query="INSERT INTO Vare ";

		if (wClause.length()>0)
		{
			query=query+ wClause;
			
			Statement stmt;
			
			try
			{
				stmt = con.createStatement(); 
				stmt.executeUpdate(query); 
				con.commit(); 
				stmt.close();
			}
			catch (Exception e)
			{ 
				e.printStackTrace(); 
			}
		}
		else
		{
		}
	}
	
	public void createVare(Vare v, ArrayList<Varegenre> vgList)
	{
		String vareGenre = opretVgList(vgList);
		String wClause = "VALUES " 
	        + "('" + v.getNavn() + "',"
			+ "'" + v.getBeskrivelse() + "',"
	        + "" + v.getRating() + ","
			+ "'" + v.getAldersgruppe() + "',"
			+ "'" + v.getUdgivelsesdato() + "',"
			+ "" + v.getUdlejningstype().getUdlejningstypeid() + ")";
		String extra = " DECLARE @ids int select @ids = MAX(vareid) from Vare ";
		String formater = " INSERT into Format VALUES ";
		int index =0;
		for(Format f : v.getFormater())
		{
			if(index>0)
			{
				formater+=",";
			}
			formater+=
				"('" + f.getMedie() + "',"
			    + "" + f.getAntal() + ","
			    + "" + f.getPris() + ","
			    + " @ids )";
			index++;
		}
	        wClause+=extra;
	        wClause+=formater;
	        wClause+=vareGenre;
	        create(wClause);
	}
	
	private String opretVgList(ArrayList<Varegenre> vgList) 
	{
		String vareGenre = "INSERT into Vare_Varegenre values ";
		int i =0;
		for(Varegenre vg : vgList)
		{
			if(i>0)
			{
				vareGenre+=",";
			}
			vareGenre+="( @ids ,"+
			""+ vg.getVaregenreid()+")";
			i++;
		}
		vareGenre+="   ";
		return vareGenre;
	}

	public void updateVare(Vare v)
	{
		PreparedStatement prepstmt;
		try 
		{     
		  prepstmt = con.prepareStatement
		   ("UPDATE Vare SET " 
				+ "Navn = '" + v.getNavn() + "',"
		   		+ "Beskrivelse = '" + v.getBeskrivelse() + "',"
		   		+ "Antal = '" + v.getAntal() + "',"
		   		+ "Aldersgruppe = '" + v.getAldersgruppe() + "',"
		   		+ "Rating = " + v.getRating() + ","
		   		+ "Udgivelsesdato = " + v.getUdgivelsesdato() + ""
		   	+ " WHERE Vareid = " + v.getVareid() + "");
		  prepstmt.executeUpdate();
		             
		  con.commit();
		  prepstmt.close();
		}
		catch (Exception e) 
		{
		  e.printStackTrace();
		}
	}
	
	public void deleteVare(int vareId)
	{
		PreparedStatement prepstmt;
		try 
		{     
		  prepstmt = con.prepareStatement
		    ("DELETE FROM Vare WHERE vareId = " + vareId + "");   
		  prepstmt.executeUpdate();          
		  con.commit();
		  prepstmt.close();
		}
		catch (Exception e) 
		{
		  e.printStackTrace();
		}
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Vare";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	private Vare singleWhere(String wClause, boolean rA)
	{
		ResultSet results;
		Vare vare = new Vare();
	    String query =  buildQuery(wClause);
		try{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            vare = buildVare(results, rA);
                            //association is to be build
                            stmt.close();
			}
		
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return vare;
	}
	
	public Vare findVareNavn(String navn) 
    {
    	String wClause = "navn = '" + navn + "' ";
    	return singleWhere(wClause, true);
    }
	
	public Vare findVareId(int id, boolean rA)
	{
		String wClause = " vareid = " + Integer.toString(id) + "";
		return singleWhere(wClause,rA);
	}
	
	public ArrayList<Vare> getAlleVare()
	{
		return miscWhere("");
	}
	
	private ArrayList<Vare> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList list = new ArrayList();	
		
		String query =  buildQuery(wClause);
  
  		try
  		{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);		 		

			while( results.next() )
			{
		     	 Vare vareObj = new Vare();
		     	 vareObj = buildVare(results, true);	
                 list.add(vareObj);	
			}//end while
                        // if retrieveAssociation is true, the ZipCity has to be selected for each object in the list
			stmt.close();

		}//slut try	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	private Vare buildVare(ResultSet results, boolean rA)
    {   
		DBFormat dbFor = DBFormat.getInstance();
		DBVaregenre_Vare dbVvg = DBVaregenre_Vare.getInstance();
		Vare vare = new Vare();
		
          try
          {
        	  	vare.setVareid(results.getInt(1));
                vare.setNavn(results.getString(2));
                vare.setBeskrivelse(results.getString(3));
                vare.setRating(results.getInt(4));
                vare.setAldersgruppe(results.getString(5));
                vare.setUdgivelsesdato(results.getString(6));
                vare.setUdlejningstype(dbUdl.findUdlejningstypeId(results.getInt(7)));
                if(rA != false)
                {
                	vare.setVvg(dbVvg.findVaregenreViaVare(results.getInt(1), false));
                    vare.setFormater(dbFor.findFormatViaVare(results.getInt(1), false));
                }
          }
         catch(Exception e)
         {
             System.out.println("error in building the vare object");
         }
         return vare;
    }

	public ArrayList<Udlej_Salg_Vare> findVarePerson(int personid) 
	{
		String query="SELECT * FROM Vare where " + personid + "= kundeid";
		return null;
	}
	
}
